import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as path;
import 'package:sqflite_demo/model/Chapter.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  // 外部使用的实例
  factory DatabaseHelper() => _instance;

  static Database? _database;

  DatabaseHelper._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // 首次加载的初始化
  Future<Database> _initDatabase() async {
    final directory = await getApplicationDocumentsDirectory();
    final dbPath = path.join(directory.path, 'books.db');

    return await openDatabase(
      dbPath, // 自动选取路径 一般为 data/data/flutterApp/books.db
      version: 1, // 只创建一次
      onCreate: _onCreate,
    );
  }

  // 表的建立
  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE books(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT NOT NULL
      )
    ''');

    await db.execute('''
      CREATE TABLE chapters(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        content TEXT,
        FOREIGN KEY(book_id) REFERENCES books(id)
      )
    ''');
  }

  // Book CRUD operations 插件简化版
  Future<int> insertBook(Map<String, dynamic> book) async {
    final db = await database;
    return await db.insert('books', book);
  }

  Future<List<Map<String, dynamic>>> getBooks() async {
    final db = await database;
    return await db.query('books');
  }

  Future<int> updateBook(Map<String, dynamic> book) async {
    final db = await database;
    return await db.update('books', book, where: 'id = ?', whereArgs: [book['id']]);
  }

  Future<int> deleteBook(int id) async {
    final db = await database;
    return await db.delete('books', where: 'id = ?', whereArgs: [id]);
  }


  // Chapter CRUD operations
  Future<int> insertChapter(Map<String, dynamic> chapter) async {
    final db = await database;
    return await db.insert('chapters', chapter);
  }

  Future<List<Map<String, dynamic>>> getChaptersByBookId(int bookId) async {
    final db = await database;
    return await db.query('chapters', where: 'book_id = ?', whereArgs: [bookId]);
  }

  Future<List<Map<String, dynamic>>> loadChapters(int bookId) async {
    final List<Map<String, dynamic>> chapters = await _database!.query(
      'chapters',
      columns: ['id', 'title'], // 只加载标题
      where: 'book_id = ?',
      whereArgs: [bookId],
      orderBy: 'id ASC',
    );
    return chapters;
  }

  Future<String?> loadChapterContent(int chapterId) async {
    if (_database == null) return null;

    final List<Map<String, dynamic>> result = await _database!.query(
      'chapters',
      columns: ['content'],
      where: 'id = ?',
      whereArgs: [chapterId],
      limit: 1,
    );
    return result.isNotEmpty ? result.first['content'] : null;
  }

  Future<int> updateChapter(Map<String, dynamic> chapter) async {
    final db = await database;
    return await db.update('chapters', chapter, where: 'id = ?', whereArgs: [chapter['id']]);
  }

  Future<int> deleteChapter(int id) async {
    final db = await database;
    return await db.delete('chapters', where: 'id = ?', whereArgs: [id]);
  }

  // 删除书籍的时候同步删除对应章节
  Future<String> deleteBookWithChapter(int bookId) async {
    if(bookId == -1){
      return "书籍不存在";
    }
    final db = await database;
    try {
      // 调用事务
      await db.transaction((txn) async {
        // 删除章节 由于外键 所以先删章节。
        await txn.delete('chapters', where: 'book_id = ?', whereArgs: [bookId]);
        // 删除书籍
        await txn.delete('books', where: 'id = ?', whereArgs: [bookId]);
      });
      return "书籍及其章节已成功删除";
    } catch (e) {
      // 捕获并反馈异常
      return "删除操作失败: ${e.toString()}";
    }
  }

  // 章节的批量插入
  Future<bool> insertAllChapter(List<Chapter> chapters) async {
    if (chapters.isEmpty) {
      return false; // 如果列表为空，直接返回 false
    }

    final db = await database; // 获取数据库实例
    try {
      // 使用事务进行批量插入
      await db.transaction((txn) async {
        for (final chapter in chapters) {
          // 插入每一条记录
          await txn.insert('chapters', chapter.toMap(),
              conflictAlgorithm: ConflictAlgorithm.replace); // 可根据需要调整冲突算法
        }
      });
      return true; // 如果事务成功完成，返回 true
    } catch (e) {
      return false; // 如果发生异常，返回 false
    }
  }
}





























